SELECT t.*
FROM c1_role t
WHERE EXISTS
  (SELECT 1 FROM c1_role_user e WHERE e.role_id = t.id AND e.user_id = :userId
  )
OR EXISTS
  (SELECT 1
  FROM c1_us_group_role e
  WHERE e.role_id = t.id
  AND EXISTS
    (SELECT 1
    FROM c1_us_group_user e_e
    WHERE e_e.user_group_id = e.user_group_id
    AND e_e.user_id         = :userId
    )
  )
ORDER BY t.org_id,
  t.department_id,
  t.order_num